﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<全部工作日志列表>
-- =============================================
CREATE PROCEDURE [dbo].[proc_WorkLog_ALL_GetList]
(
	@LogType INT,
	@StartIndex int,
	@EndIndex INT,
	@CompanyId INT,
	@DepId INT,
	@Stext NVARCHAR(200),
	@StartDate NVARCHAR(10),
	@EndDate NVARCHAR(10)
)
AS
BEGIN
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId

If @LogType=1
Begin
	;WITH List As(Select ROW_NUMBER() OVER (Order BY WorkLog.CreateTime Desc) AS Row,
		id,
		GID, 
		Title, 
		[Content], 
		CreateTime, 
		EditTime, 
		UserName, 
		Name, 
		CompanyId,
		ISNULL((SELECT Company.CompanyName FROM Company WHERE Company.Id=worklog.CompanyId),'') AS CompanyName,
		DepId, 
		ISNULL((SELECT Department.DName  FROM Department WHERE Department.Id=WorkLog.DepId),'') AS DepName,
		LogType
	    FROM WorkLog
	    WHERE LogType In(1,3)
	    AND (@CompanyId=0 Or CompanyId=@CompanyId) And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
	    And CreateTime>=@sRq And CreateTime<=@eRq
		And (@Stext='' Or Title like '%'+@Stext+'%' Or Name like '%'+@Stext+'%')
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By Row
End
Else
Begin
	;WITH List As(Select ROW_NUMBER() OVER (Order BY WorkLog.CreateTime Desc) AS Row,
		id,
		GID, 
		Title, 
		[Content], 
		CreateTime, 
		EditTime, 
		UserName, 
		Name, 
		CompanyId,
		ISNULL((SELECT Company.CompanyName FROM Company WHERE Company.Id=worklog.CompanyId),'') AS CompanyName,
		DepId, 
		ISNULL((SELECT Department.DName  FROM Department WHERE Department.Id=WorkLog.DepId),'') AS DepName,
		LogType
	    FROM WorkLog
	    WHERE LogType=@LogType 
	    AND (@CompanyId=0 Or CompanyId=@CompanyId) And (@DepId=0 Or DepId In(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
	    And CreateTime>=@sRq And CreateTime<=@eRq
		And (@Stext='' Or Title like '%'+@Stext+'%' Or Name like '%'+@Stext+'%')
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between @StartIndex and @EndIndex Order By Row
End
END
